{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "cell-0",
   "metadata": {},
   "source": [
    "# Pivot Vehicle Counts by EVA Fuel Type\n",
    "\n",
    "This notebook reads each **VehicleCountsByEVAFuelType** table (one per geographic\n",
    "level), pivots the `EVAFuelType` values into separate columns so that each row\n",
    "represents a single location, and then adds two derived columns:\n",
    "\n",
    "| Column | Description |\n",
    "|---|---|\n",
    "| **TotalVehicleCount** | Sum of all fuel-type counts for the location |\n",
    "| **BEVProportion** | `BEV / TotalVehicleCount` — share of battery-electric vehicles |\n",
    "\n",
    "### Geographic levels processed\n",
    "\n",
    "| Level | Input file | Location column | Output file |\n",
    "|---|---|---|---|\n",
    "| US States | `VehicleCountsByEVAFuelType_USStates.csv` | State | `Crosstab_USStates.csv` |\n",
    "| AL Counties | `VehicleCountsByEVAFuelType_ALCounties.csv` | County | `Crosstab_ALCounties.csv` |\n",
    "| AL ZIP Codes | `VehicleCountsByEVAFuelType_ALZipCodes.csv` | ZipCode | `Crosstab_ALZipCodes.csv` |\n",
    "\n",
    "### EVA Fuel Type categories\n",
    "\n",
    "| Code | Meaning |\n",
    "|---|---|\n",
    "| BEV | Battery Electric Vehicle |\n",
    "| GDV | Gasoline/Diesel Vehicle |\n",
    "| HEV | Hybrid Electric Vehicle |\n",
    "| PHEV | Plug-in Hybrid Electric Vehicle |\n",
    "| HFCV | Hydrogen Fuel Cell Vehicle (US States only) |"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "cell-1",
   "metadata": {},
   "outputs": [],
   "source": [
    "import polars as pl"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-2",
   "metadata": {},
   "source": [
    "## Helper function\n",
    "\n",
    "`pivot_and_enrich` performs three operations on a long-format vehicle-count table:\n",
    "\n",
    "1. **Pivot** — moves `EVAFuelType` values into columns so each location occupies\n",
    "   exactly one row.  Any fuel types absent for a location are filled with `0`.\n",
    "2. **TotalVehicleCount** — sums every fuel-type column to give the total registered\n",
    "   vehicles at each location.\n",
    "3. **BEVProportion** — divides the BEV count by the total, giving the share of\n",
    "   battery-electric vehicles.  Locations with zero total vehicles receive a\n",
    "   proportion of `0.0` to avoid division-by-zero errors."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "cell-3",
   "metadata": {},
   "outputs": [],
   "source": [
    "def pivot_and_enrich(df: pl.DataFrame, location_col: str) -> pl.DataFrame:\n",
    "    \"\"\"\n",
    "    Pivot a long-format vehicle count table and add TotalVehicleCount and BEVProportion.\n",
    "\n",
    "    Parameters\n",
    "    ----------\n",
    "    df : pl.DataFrame\n",
    "        Must contain columns: `location_col`, \"EVAFuelType\", \"Count\".\n",
    "    location_col : str\n",
    "        Name of the column that identifies locations (e.g. \"State\", \"County\", \"ZipCode\").\n",
    "\n",
    "    Returns\n",
    "    -------\n",
    "    pl.DataFrame\n",
    "        One row per location with fuel-type columns, TotalVehicleCount, and BEVProportion.\n",
    "    \"\"\"\n",
    "    # Pivot: location rows x fuel-type columns\n",
    "    pivoted = df.pivot(\n",
    "        on=\"EVAFuelType\",\n",
    "        index=location_col,\n",
    "        values=\"Count\",\n",
    "    ).fill_null(0)\n",
    "\n",
    "    # Identify the fuel-type columns (everything except the location column)\n",
    "    fuel_cols = [c for c in pivoted.columns if c != location_col]\n",
    "\n",
    "    # Add TotalVehicleCount and BEVProportion\n",
    "    pivoted = pivoted.with_columns(\n",
    "        pl.sum_horizontal(fuel_cols).alias(\"TotalVehicleCount\"),\n",
    "    ).with_columns(\n",
    "        (pl.col(\"BEV\") / pl.col(\"TotalVehicleCount\"))\n",
    "        .fill_nan(0.0)\n",
    "        .alias(\"BEVProportion\"),\n",
    "    )\n",
    "\n",
    "    return pivoted.sort(location_col)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-4",
   "metadata": {},
   "source": [
    "## 1. US States\n",
    "\n",
    "The US States table contains five fuel types (BEV, GDV, HEV, HFCV, PHEV) across\n",
    "all 50 states plus the District of Columbia."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "cell-5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Input shape: (255, 3)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (5, 3)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>State</th><th>EVAFuelType</th><th>Count</th></tr><tr><td>str</td><td>str</td><td>i64</td></tr></thead><tbody><tr><td>&quot;Alabama&quot;</td><td>&quot;BEV&quot;</td><td>17500</td></tr><tr><td>&quot;Alabama&quot;</td><td>&quot;GDV&quot;</td><td>4779800</td></tr><tr><td>&quot;Alabama&quot;</td><td>&quot;HEV&quot;</td><td>77600</td></tr><tr><td>&quot;Alabama&quot;</td><td>&quot;HFCV&quot;</td><td>0</td></tr><tr><td>&quot;Alabama&quot;</td><td>&quot;PHEV&quot;</td><td>6800</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (5, 3)\n",
       "┌─────────┬─────────────┬─────────┐\n",
       "│ State   ┆ EVAFuelType ┆ Count   │\n",
       "│ ---     ┆ ---         ┆ ---     │\n",
       "│ str     ┆ str         ┆ i64     │\n",
       "╞═════════╪═════════════╪═════════╡\n",
       "│ Alabama ┆ BEV         ┆ 17500   │\n",
       "│ Alabama ┆ GDV         ┆ 4779800 │\n",
       "│ Alabama ┆ HEV         ┆ 77600   │\n",
       "│ Alabama ┆ HFCV        ┆ 0       │\n",
       "│ Alabama ┆ PHEV        ┆ 6800    │\n",
       "└─────────┴─────────────┴─────────┘"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "states_long = pl.read_csv(\"../Input/VehicleCountsByEVAFuelType_USStates.csv\")\n",
    "print(\"Input shape:\", states_long.shape)\n",
    "states_long.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "cell-6",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Output shape: (51, 8)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (10, 8)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>State</th><th>BEV</th><th>GDV</th><th>HEV</th><th>HFCV</th><th>PHEV</th><th>TotalVehicleCount</th><th>BEVProportion</th></tr><tr><td>str</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>f64</td></tr></thead><tbody><tr><td>&quot;Alabama&quot;</td><td>17500</td><td>4779800</td><td>77600</td><td>0</td><td>6800</td><td>4881700</td><td>0.003585</td></tr><tr><td>&quot;Alaska&quot;</td><td>3400</td><td>553500</td><td>12100</td><td>0</td><td>1100</td><td>570100</td><td>0.005964</td></tr><tr><td>&quot;Arizona&quot;</td><td>111200</td><td>6244200</td><td>202200</td><td>0</td><td>29300</td><td>6586900</td><td>0.016882</td></tr><tr><td>&quot;Arkansas&quot;</td><td>9500</td><td>2679400</td><td>44500</td><td>0</td><td>3800</td><td>2737200</td><td>0.003471</td></tr><tr><td>&quot;California&quot;</td><td>1533900</td><td>33535700</td><td>1895100</td><td>16900</td><td>447100</td><td>37428700</td><td>0.040982</td></tr><tr><td>&quot;Colorado&quot;</td><td>127000</td><td>5147100</td><td>171200</td><td>0</td><td>48700</td><td>5494000</td><td>0.023116</td></tr><tr><td>&quot;Connecticut&quot;</td><td>39400</td><td>2775600</td><td>85200</td><td>0</td><td>22500</td><td>2922700</td><td>0.013481</td></tr><tr><td>&quot;Delaware&quot;</td><td>11100</td><td>877900</td><td>26400</td><td>0</td><td>4600</td><td>920000</td><td>0.012065</td></tr><tr><td>&quot;District of Columbia&quot;</td><td>10100</td><td>274400</td><td>19300</td><td>0</td><td>4300</td><td>308100</td><td>0.032782</td></tr><tr><td>&quot;Florida&quot;</td><td>334800</td><td>17838000</td><td>499300</td><td>0</td><td>70400</td><td>18742500</td><td>0.017863</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (10, 8)\n",
       "┌──────────────────────┬─────────┬──────────┬─────────┬───────┬────────┬───────────────────┬───────────────┐\n",
       "│ State                ┆ BEV     ┆ GDV      ┆ HEV     ┆ HFCV  ┆ PHEV   ┆ TotalVehicleCount ┆ BEVProportion │\n",
       "│ ---                  ┆ ---     ┆ ---      ┆ ---     ┆ ---   ┆ ---    ┆ ---               ┆ ---           │\n",
       "│ str                  ┆ i64     ┆ i64      ┆ i64     ┆ i64   ┆ i64    ┆ i64               ┆ f64           │\n",
       "╞══════════════════════╪═════════╪══════════╪═════════╪═══════╪════════╪═══════════════════╪═══════════════╡\n",
       "│ Alabama              ┆ 17500   ┆ 4779800  ┆ 77600   ┆ 0     ┆ 6800   ┆ 4881700           ┆ 0.003585      │\n",
       "│ Alaska               ┆ 3400    ┆ 553500   ┆ 12100   ┆ 0     ┆ 1100   ┆ 570100            ┆ 0.005964      │\n",
       "│ Arizona              ┆ 111200  ┆ 6244200  ┆ 202200  ┆ 0     ┆ 29300  ┆ 6586900           ┆ 0.016882      │\n",
       "│ Arkansas             ┆ 9500    ┆ 2679400  ┆ 44500   ┆ 0     ┆ 3800   ┆ 2737200           ┆ 0.003471      │\n",
       "│ California           ┆ 1533900 ┆ 33535700 ┆ 1895100 ┆ 16900 ┆ 447100 ┆ 37428700          ┆ 0.040982      │\n",
       "│ Colorado             ┆ 127000  ┆ 5147100  ┆ 171200  ┆ 0     ┆ 48700  ┆ 5494000           ┆ 0.023116      │\n",
       "│ Connecticut          ┆ 39400   ┆ 2775600  ┆ 85200   ┆ 0     ┆ 22500  ┆ 2922700           ┆ 0.013481      │\n",
       "│ Delaware             ┆ 11100   ┆ 877900   ┆ 26400   ┆ 0     ┆ 4600   ┆ 920000            ┆ 0.012065      │\n",
       "│ District of Columbia ┆ 10100   ┆ 274400   ┆ 19300   ┆ 0     ┆ 4300   ┆ 308100            ┆ 0.032782      │\n",
       "│ Florida              ┆ 334800  ┆ 17838000 ┆ 499300  ┆ 0     ┆ 70400  ┆ 18742500          ┆ 0.017863      │\n",
       "└──────────────────────┴─────────┴──────────┴─────────┴───────┴────────┴───────────────────┴───────────────┘"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "states_wide = pivot_and_enrich(states_long, \"State\")\n",
    "print(\"Output shape:\", states_wide.shape)\n",
    "states_wide.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "cell-7",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Saved Crosstab_USStates.csv\n"
     ]
    }
   ],
   "source": [
    "states_wide.write_csv(\"Crosstab_USStates.csv\")\n",
    "print(\"Saved Crosstab_USStates.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-8",
   "metadata": {},
   "source": [
    "## 2. Alabama Counties\n",
    "\n",
    "The Alabama Counties table contains four fuel types (BEV, GDV, HEV, PHEV) for\n",
    "each of the 67 Alabama counties.  HFCV is not present at this level."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "cell-9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Input shape: (268, 3)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (5, 3)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>County</th><th>EVAFuelType</th><th>Count</th></tr><tr><td>str</td><td>str</td><td>i64</td></tr></thead><tbody><tr><td>&quot;Baldwin&quot;</td><td>&quot;BEV&quot;</td><td>1716</td></tr><tr><td>&quot;Bibb&quot;</td><td>&quot;BEV&quot;</td><td>23</td></tr><tr><td>&quot;Bullock&quot;</td><td>&quot;BEV&quot;</td><td>8</td></tr><tr><td>&quot;Butler&quot;</td><td>&quot;BEV&quot;</td><td>25</td></tr><tr><td>&quot;Chambers&quot;</td><td>&quot;BEV&quot;</td><td>54</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (5, 3)\n",
       "┌──────────┬─────────────┬───────┐\n",
       "│ County   ┆ EVAFuelType ┆ Count │\n",
       "│ ---      ┆ ---         ┆ ---   │\n",
       "│ str      ┆ str         ┆ i64   │\n",
       "╞══════════╪═════════════╪═══════╡\n",
       "│ Baldwin  ┆ BEV         ┆ 1716  │\n",
       "│ Bibb     ┆ BEV         ┆ 23    │\n",
       "│ Bullock  ┆ BEV         ┆ 8     │\n",
       "│ Butler   ┆ BEV         ┆ 25    │\n",
       "│ Chambers ┆ BEV         ┆ 54    │\n",
       "└──────────┴─────────────┴───────┘"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "counties_long = pl.read_csv(\"../Input/VehicleCountsByEVAFuelType_ALCounties.csv\")\n",
    "print(\"Input shape:\", counties_long.shape)\n",
    "counties_long.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "cell-10",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Output shape: (67, 7)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (10, 7)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>County</th><th>BEV</th><th>GDV</th><th>HEV</th><th>PHEV</th><th>TotalVehicleCount</th><th>BEVProportion</th></tr><tr><td>str</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>f64</td></tr></thead><tbody><tr><td>&quot;Autauga&quot;</td><td>307</td><td>58549</td><td>1608</td><td>104</td><td>60568</td><td>0.005069</td></tr><tr><td>&quot;Baldwin&quot;</td><td>1716</td><td>264256</td><td>9536</td><td>667</td><td>276175</td><td>0.006213</td></tr><tr><td>&quot;Barbour&quot;</td><td>31</td><td>24020</td><td>378</td><td>10</td><td>24439</td><td>0.001268</td></tr><tr><td>&quot;Bibb&quot;</td><td>23</td><td>23880</td><td>378</td><td>14</td><td>24295</td><td>0.000947</td></tr><tr><td>&quot;Blount&quot;</td><td>111</td><td>62822</td><td>1087</td><td>38</td><td>64058</td><td>0.001733</td></tr><tr><td>&quot;Bullock&quot;</td><td>8</td><td>9408</td><td>106</td><td>1</td><td>9523</td><td>0.00084</td></tr><tr><td>&quot;Butler&quot;</td><td>25</td><td>19125</td><td>270</td><td>6</td><td>19426</td><td>0.001287</td></tr><tr><td>&quot;Calhoun&quot;</td><td>332</td><td>109743</td><td>2372</td><td>83</td><td>112530</td><td>0.00295</td></tr><tr><td>&quot;Chambers&quot;</td><td>54</td><td>38490</td><td>475</td><td>12</td><td>39031</td><td>0.001384</td></tr><tr><td>&quot;Cherokee&quot;</td><td>60</td><td>31049</td><td>583</td><td>22</td><td>31714</td><td>0.001892</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (10, 7)\n",
       "┌──────────┬──────┬────────┬──────┬──────┬───────────────────┬───────────────┐\n",
       "│ County   ┆ BEV  ┆ GDV    ┆ HEV  ┆ PHEV ┆ TotalVehicleCount ┆ BEVProportion │\n",
       "│ ---      ┆ ---  ┆ ---    ┆ ---  ┆ ---  ┆ ---               ┆ ---           │\n",
       "│ str      ┆ i64  ┆ i64    ┆ i64  ┆ i64  ┆ i64               ┆ f64           │\n",
       "╞══════════╪══════╪════════╪══════╪══════╪═══════════════════╪═══════════════╡\n",
       "│ Autauga  ┆ 307  ┆ 58549  ┆ 1608 ┆ 104  ┆ 60568             ┆ 0.005069      │\n",
       "│ Baldwin  ┆ 1716 ┆ 264256 ┆ 9536 ┆ 667  ┆ 276175            ┆ 0.006213      │\n",
       "│ Barbour  ┆ 31   ┆ 24020  ┆ 378  ┆ 10   ┆ 24439             ┆ 0.001268      │\n",
       "│ Bibb     ┆ 23   ┆ 23880  ┆ 378  ┆ 14   ┆ 24295             ┆ 0.000947      │\n",
       "│ Blount   ┆ 111  ┆ 62822  ┆ 1087 ┆ 38   ┆ 64058             ┆ 0.001733      │\n",
       "│ Bullock  ┆ 8    ┆ 9408   ┆ 106  ┆ 1    ┆ 9523              ┆ 0.00084       │\n",
       "│ Butler   ┆ 25   ┆ 19125  ┆ 270  ┆ 6    ┆ 19426             ┆ 0.001287      │\n",
       "│ Calhoun  ┆ 332  ┆ 109743 ┆ 2372 ┆ 83   ┆ 112530            ┆ 0.00295       │\n",
       "│ Chambers ┆ 54   ┆ 38490  ┆ 475  ┆ 12   ┆ 39031             ┆ 0.001384      │\n",
       "│ Cherokee ┆ 60   ┆ 31049  ┆ 583  ┆ 22   ┆ 31714             ┆ 0.001892      │\n",
       "└──────────┴──────┴────────┴──────┴──────┴───────────────────┴───────────────┘"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "counties_wide = pivot_and_enrich(counties_long, \"County\")\n",
    "print(\"Output shape:\", counties_wide.shape)\n",
    "counties_wide.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "cell-11",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Saved Crosstab_ALCounties.csv\n"
     ]
    }
   ],
   "source": [
    "counties_wide.write_csv(\"Crosstab_ALCounties.csv\")\n",
    "print(\"Saved Crosstab_ALCounties.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-12",
   "metadata": {},
   "source": [
    "## 3. Alabama ZIP Codes\n",
    "\n",
    "The Alabama ZIP Codes table contains four fuel types (BEV, GDV, HEV, PHEV) for\n",
    "each ZIP code with registered vehicles in Alabama."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "cell-13",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Input shape: (2373, 3)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (5, 3)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>ZipCode</th><th>EVAFuelType</th><th>Count</th></tr><tr><td>i64</td><td>str</td><td>i64</td></tr></thead><tbody><tr><td>35004</td><td>&quot;BEV&quot;</td><td>55</td></tr><tr><td>35006</td><td>&quot;BEV&quot;</td><td>4</td></tr><tr><td>35010</td><td>&quot;BEV&quot;</td><td>44</td></tr><tr><td>35016</td><td>&quot;BEV&quot;</td><td>73</td></tr><tr><td>35020</td><td>&quot;BEV&quot;</td><td>22</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (5, 3)\n",
       "┌─────────┬─────────────┬───────┐\n",
       "│ ZipCode ┆ EVAFuelType ┆ Count │\n",
       "│ ---     ┆ ---         ┆ ---   │\n",
       "│ i64     ┆ str         ┆ i64   │\n",
       "╞═════════╪═════════════╪═══════╡\n",
       "│ 35004   ┆ BEV         ┆ 55    │\n",
       "│ 35006   ┆ BEV         ┆ 4     │\n",
       "│ 35010   ┆ BEV         ┆ 44    │\n",
       "│ 35016   ┆ BEV         ┆ 73    │\n",
       "│ 35020   ┆ BEV         ┆ 22    │\n",
       "└─────────┴─────────────┴───────┘"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zipcodes_long = pl.read_csv(\"../Input/VehicleCountsByEVAFuelType_ALZipCodes.csv\")\n",
    "print(\"Input shape:\", zipcodes_long.shape)\n",
    "zipcodes_long.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "cell-14",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Output shape: (757, 7)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (10, 7)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>ZipCode</th><th>BEV</th><th>GDV</th><th>HEV</th><th>PHEV</th><th>TotalVehicleCount</th><th>BEVProportion</th></tr><tr><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>i64</td><td>f64</td></tr></thead><tbody><tr><td>35004</td><td>55</td><td>11075</td><td>332</td><td>18</td><td>11480</td><td>0.004791</td></tr><tr><td>35005</td><td>14</td><td>7359</td><td>124</td><td>3</td><td>7500</td><td>0.001867</td></tr><tr><td>35006</td><td>4</td><td>3292</td><td>44</td><td>2</td><td>3342</td><td>0.001197</td></tr><tr><td>35007</td><td>134</td><td>26722</td><td>745</td><td>35</td><td>27636</td><td>0.004849</td></tr><tr><td>35010</td><td>44</td><td>19750</td><td>440</td><td>21</td><td>20255</td><td>0.002172</td></tr><tr><td>35011</td><td>0</td><td>80</td><td>0</td><td>0</td><td>80</td><td>0.0</td></tr><tr><td>35013</td><td>0</td><td>3</td><td>0</td><td>0</td><td>3</td><td>0.0</td></tr><tr><td>35014</td><td>8</td><td>4789</td><td>89</td><td>4</td><td>4890</td><td>0.001636</td></tr><tr><td>35016</td><td>73</td><td>18534</td><td>430</td><td>25</td><td>19062</td><td>0.00383</td></tr><tr><td>35019</td><td>3</td><td>2728</td><td>43</td><td>5</td><td>2779</td><td>0.00108</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (10, 7)\n",
       "┌─────────┬─────┬───────┬─────┬──────┬───────────────────┬───────────────┐\n",
       "│ ZipCode ┆ BEV ┆ GDV   ┆ HEV ┆ PHEV ┆ TotalVehicleCount ┆ BEVProportion │\n",
       "│ ---     ┆ --- ┆ ---   ┆ --- ┆ ---  ┆ ---               ┆ ---           │\n",
       "│ i64     ┆ i64 ┆ i64   ┆ i64 ┆ i64  ┆ i64               ┆ f64           │\n",
       "╞═════════╪═════╪═══════╪═════╪══════╪═══════════════════╪═══════════════╡\n",
       "│ 35004   ┆ 55  ┆ 11075 ┆ 332 ┆ 18   ┆ 11480             ┆ 0.004791      │\n",
       "│ 35005   ┆ 14  ┆ 7359  ┆ 124 ┆ 3    ┆ 7500              ┆ 0.001867      │\n",
       "│ 35006   ┆ 4   ┆ 3292  ┆ 44  ┆ 2    ┆ 3342              ┆ 0.001197      │\n",
       "│ 35007   ┆ 134 ┆ 26722 ┆ 745 ┆ 35   ┆ 27636             ┆ 0.004849      │\n",
       "│ 35010   ┆ 44  ┆ 19750 ┆ 440 ┆ 21   ┆ 20255             ┆ 0.002172      │\n",
       "│ 35011   ┆ 0   ┆ 80    ┆ 0   ┆ 0    ┆ 80                ┆ 0.0           │\n",
       "│ 35013   ┆ 0   ┆ 3     ┆ 0   ┆ 0    ┆ 3                 ┆ 0.0           │\n",
       "│ 35014   ┆ 8   ┆ 4789  ┆ 89  ┆ 4    ┆ 4890              ┆ 0.001636      │\n",
       "│ 35016   ┆ 73  ┆ 18534 ┆ 430 ┆ 25   ┆ 19062             ┆ 0.00383       │\n",
       "│ 35019   ┆ 3   ┆ 2728  ┆ 43  ┆ 5    ┆ 2779              ┆ 0.00108       │\n",
       "└─────────┴─────┴───────┴─────┴──────┴───────────────────┴───────────────┘"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "zipcodes_wide = pivot_and_enrich(zipcodes_long, \"ZipCode\")\n",
    "print(\"Output shape:\", zipcodes_wide.shape)\n",
    "zipcodes_wide.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "cell-15",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Saved Crosstab_ALZipCodes.csv\n"
     ]
    }
   ],
   "source": [
    "zipcodes_wide.write_csv(\"Crosstab_ALZipCodes.csv\")\n",
    "print(\"Saved Crosstab_ALZipCodes.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cell-16",
   "metadata": {},
   "source": [
    "## Summary\n",
    "\n",
    "Three crosstabulation files have been written to the `Crosstabulation/` folder:\n",
    "\n",
    "| File | Rows | Columns |\n",
    "|---|---|---|\n",
    "| `Crosstab_USStates.csv` | 51 (50 states + DC) | State, BEV, GDV, HEV, HFCV, PHEV, TotalVehicleCount, BEVProportion |\n",
    "| `Crosstab_ALCounties.csv` | 67 counties | County, BEV, GDV, HEV, PHEV, TotalVehicleCount, BEVProportion |\n",
    "| `Crosstab_ALZipCodes.csv` | ~593 ZIP codes | ZipCode, BEV, GDV, HEV, PHEV, TotalVehicleCount, BEVProportion |\n",
    "\n",
    "Each file has one row per location. The fuel-type columns contain vehicle counts,\n",
    "`TotalVehicleCount` is their sum, and `BEVProportion` is `BEV / TotalVehicleCount`."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e64d1305",
   "metadata": {},
   "source": [
    "# ZIP Code Comparisons: ACS Demographics + EV Registrations\n",
    "\n",
    "This notebook joins demographic data from the American Community Survey (ACS) with\n",
    "vehicle-registration crosstabulation data to produce a single ZIP-code-level\n",
    "comparison table.\n",
    "\n",
    "### Inputs\n",
    "\n",
    "| Source | File | Description |\n",
    "|---|---|---|\n",
    "| ACS 5-Year | `../DownloadACS/ACS_ALZipCodes.csv` | Transposed ACS data — variables in rows, ZIP codes in columns |\n",
    "| Crosstabulation | `Crosstab_ALZipCodes.csv` | Pivoted vehicle counts produced by `PivotEVAFuelType.ipynb` |\n",
    "\n",
    "### Variables extracted from ACS\n",
    "\n",
    "| Variable | Description |\n",
    "|---|---|\n",
    "| `B01003_E001` | Total population |\n",
    "| `LandArea` | Land area in square miles |\n",
    "| `PopulationDensity` | Land area per capita (square miles per person) |\n",
    "\n",
    "### Columns extracted from Crosstabulation\n",
    "\n",
    "| Column | Description |\n",
    "|---|---|\n",
    "| `ZipCode` | ZIP code identifier |\n",
    "| `BEV` | Battery Electric Vehicle count |\n",
    "| `TotalVehicleCount` | Total registered vehicles across all fuel types |\n",
    "| `BEVProportion` | BEV / TotalVehicleCount |\n",
    "\n",
    "### Join strategy\n",
    "\n",
    "An **outer join** on `ZipCode` is used so that no ZIP codes are lost from either\n",
    "source.  ZIP codes present in ACS but absent from vehicle registrations (or vice\n",
    "versa) will appear with `null` values in the columns from the other source.\n",
    "\n",
    "### Output\n",
    "\n",
    "| File | Description |\n",
    "|---|---|\n",
    "| `ZipCodeComparisons.csv` | Joined table with one row per ZIP code |"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "10a56864",
   "metadata": {},
   "outputs": [],
   "source": [
    "import polars as pl"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9a284f86",
   "metadata": {},
   "source": [
    "## 1. Extract ACS demographic variables\n",
    "\n",
    "The ACS file is in **transposed** format: each row is a variable and each column\n",
    "is a ZIP code.  We filter to the three variables of interest, transpose the\n",
    "result back to a conventional layout (one row per ZIP code), and rename the\n",
    "columns from their ACS variable codes to human-readable names."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "dffbdb5e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ACS raw shape: (656, 910)\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr,\n",
       ".dataframe > tbody > tr {\n",
       "  text-align: right;\n",
       "  white-space: pre-wrap;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (3, 910)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>Location</th><th>B01002_E001</th><th>B01002_E002</th><th>B01002_E003</th><th>B01003_E001</th><th>B02001_E001</th><th>B02001_E002</th><th>B02001_E003</th><th>B02001_E004</th><th>B02001_E005</th><th>B02001_E006</th><th>B02001_E007</th><th>B02001_E008</th><th>B02001_E009</th><th>B02001_E010</th><th>B05001_E001</th><th>B05001_E002</th><th>B05001_E003</th><th>B05001_E004</th><th>B05001_E005</th><th>B05001_E006</th><th>B05002_E001</th><th>B05002_E002</th><th>B05002_E003</th><th>B05002_E004</th><th>B05002_E005</th><th>B05002_E006</th><th>B05002_E007</th><th>B05002_E008</th><th>B05002_E009</th><th>B05002_E010</th><th>B05002_E011</th><th>B05002_E012</th><th>B05002_E013</th><th>B05002_E014</th><th>B05002_E015</th><th>B05002_E016</th><th>&hellip;</th><th>C24050_E050</th><th>C24050_E051</th><th>C24050_E052</th><th>C24050_E053</th><th>C24050_E054</th><th>C24050_E055</th><th>C24050_E056</th><th>C24050_E057</th><th>C24050_E058</th><th>C24050_E059</th><th>C24050_E060</th><th>C24050_E061</th><th>C24050_E062</th><th>C24050_E063</th><th>C24050_E064</th><th>C24050_E065</th><th>C24050_E066</th><th>C24050_E067</th><th>C24050_E068</th><th>C24050_E069</th><th>C24050_E070</th><th>C24050_E071</th><th>C24050_E072</th><th>C24050_E073</th><th>C24050_E074</th><th>C24050_E075</th><th>C24050_E076</th><th>C24050_E077</th><th>C24050_E078</th><th>C24050_E079</th><th>C24050_E080</th><th>C24050_E081</th><th>C24050_E082</th><th>C24050_E083</th><th>C24050_E084</th><th>LandArea</th><th>PopulationDensity</th></tr><tr><td>i64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>&hellip;</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td><td>f64</td></tr></thead><tbody><tr><td>35004</td><td>43.2</td><td>41.6</td><td>44.6</td><td>12155.0</td><td>12155.0</td><td>9472.0</td><td>1646.0</td><td>141.0</td><td>106.0</td><td>0.0</td><td>0.0</td><td>790.0</td><td>224.0</td><td>566.0</td><td>12155.0</td><td>11705.0</td><td>0.0</td><td>104.0</td><td>274.0</td><td>72.0</td><td>12155.0</td><td>11809.0</td><td>8042.0</td><td>3663.0</td><td>708.0</td><td>707.0</td><td>2000.0</td><td>248.0</td><td>104.0</td><td>0.0</td><td>0.0</td><td>104.0</td><td>346.0</td><td>274.0</td><td>33.0</td><td>124.0</td><td>&hellip;</td><td>16.0</td><td>341.0</td><td>123.0</td><td>341.0</td><td>65.0</td><td>102.0</td><td>0.0</td><td>476.0</td><td>0.0</td><td>283.0</td><td>0.0</td><td>0.0</td><td>78.0</td><td>23.0</td><td>0.0</td><td>10.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>41.0</td><td>41.0</td><td>797.0</td><td>0.0</td><td>0.0</td><td>219.0</td><td>64.0</td><td>201.0</td><td>155.0</td><td>38.0</td><td>0.0</td><td>102.0</td><td>18.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>17.239</td><td>0.001418</td></tr><tr><td>35005</td><td>41.7</td><td>36.9</td><td>46.2</td><td>8247.0</td><td>8247.0</td><td>3022.0</td><td>4559.0</td><td>0.0</td><td>86.0</td><td>538.0</td><td>0.0</td><td>42.0</td><td>18.0</td><td>24.0</td><td>8247.0</td><td>7955.0</td><td>0.0</td><td>0.0</td><td>30.0</td><td>262.0</td><td>8247.0</td><td>7955.0</td><td>6295.0</td><td>1660.0</td><td>63.0</td><td>659.0</td><td>795.0</td><td>143.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>292.0</td><td>30.0</td><td>2.0</td><td>28.0</td><td>&hellip;</td><td>53.0</td><td>382.0</td><td>32.0</td><td>203.0</td><td>2.0</td><td>4.0</td><td>34.0</td><td>240.0</td><td>0.0</td><td>81.0</td><td>2.0</td><td>5.0</td><td>3.0</td><td>39.0</td><td>0.0</td><td>41.0</td><td>19.0</td><td>5.0</td><td>21.0</td><td>24.0</td><td>0.0</td><td>840.0</td><td>0.0</td><td>18.0</td><td>196.0</td><td>38.0</td><td>274.0</td><td>158.0</td><td>0.0</td><td>0.0</td><td>33.0</td><td>39.0</td><td>3.0</td><td>65.0</td><td>16.0</td><td>31.747</td><td>0.00385</td></tr><tr><td>35006</td><td>38.5</td><td>38.6</td><td>38.5</td><td>3894.0</td><td>3894.0</td><td>3468.0</td><td>313.0</td><td>2.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>111.0</td><td>94.0</td><td>17.0</td><td>3894.0</td><td>3884.0</td><td>0.0</td><td>0.0</td><td>10.0</td><td>0.0</td><td>3894.0</td><td>3884.0</td><td>3418.0</td><td>466.0</td><td>56.0</td><td>97.0</td><td>293.0</td><td>20.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>10.0</td><td>10.0</td><td>0.0</td><td>0.0</td><td>&hellip;</td><td>0.0</td><td>39.0</td><td>80.0</td><td>98.0</td><td>0.0</td><td>12.0</td><td>190.0</td><td>354.0</td><td>46.0</td><td>87.0</td><td>40.0</td><td>0.0</td><td>95.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>55.0</td><td>0.0</td><td>0.0</td><td>14.0</td><td>17.0</td><td>301.0</td><td>0.0</td><td>24.0</td><td>144.0</td><td>0.0</td><td>28.0</td><td>19.0</td><td>0.0</td><td>0.0</td><td>0.0</td><td>42.0</td><td>0.0</td><td>44.0</td><td>0.0</td><td>103.993</td><td>0.026706</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (3, 910)\n",
       "┌──────────┬─────────────┬─────────────┬─────────────┬───┬─────────────┬─────────────┬──────────┬───────────────────┐\n",
       "│ Location ┆ B01002_E001 ┆ B01002_E002 ┆ B01002_E003 ┆ … ┆ C24050_E083 ┆ C24050_E084 ┆ LandArea ┆ PopulationDensity │\n",
       "│ ---      ┆ ---         ┆ ---         ┆ ---         ┆   ┆ ---         ┆ ---         ┆ ---      ┆ ---               │\n",
       "│ i64      ┆ f64         ┆ f64         ┆ f64         ┆   ┆ f64         ┆ f64         ┆ f64      ┆ f64               │\n",
       "╞══════════╪═════════════╪═════════════╪═════════════╪═══╪═════════════╪═════════════╪══════════╪═══════════════════╡\n",
       "│ 35004    ┆ 43.2        ┆ 41.6        ┆ 44.6        ┆ … ┆ 0.0         ┆ 0.0         ┆ 17.239   ┆ 0.001418          │\n",
       "│ 35005    ┆ 41.7        ┆ 36.9        ┆ 46.2        ┆ … ┆ 65.0        ┆ 16.0        ┆ 31.747   ┆ 0.00385           │\n",
       "│ 35006    ┆ 38.5        ┆ 38.6        ┆ 38.5        ┆ … ┆ 44.0        ┆ 0.0         ┆ 103.993  ┆ 0.026706          │\n",
       "└──────────┴─────────────┴─────────────┴─────────────┴───┴─────────────┴─────────────┴──────────┴───────────────────┘"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "acs_raw = pl.read_csv(\"ACS_ALZipCodes.csv\")\n",
    "print(\"ACS raw shape:\", acs_raw.shape)\n",
    "acs_raw.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "3ce776fb",
   "metadata": {},
   "outputs": [
    {
     "ename": "ColumnNotFoundError",
     "evalue": "unable to find column \"Variable\"; valid columns: [\"Location\", \"B01002_E001\", \"B01002_E002\", \"B01002_E003\", \"B01003_E001\", \"B02001_E001\", \"B02001_E002\", \"B02001_E003\", \"B02001_E004\", \"B02001_E005\", \"B02001_E006\", \"B02001_E007\", \"B02001_E008\", \"B02001_E009\", \"B02001_E010\", \"B05001_E001\", \"B05001_E002\", \"B05001_E003\", \"B05001_E004\", \"B05001_E005\", \"B05001_E006\", \"B05002_E001\", \"B05002_E002\", \"B05002_E003\", \"B05002_E004\", \"B05002_E005\", \"B05002_E006\", \"B05002_E007\", \"B05002_E008\", \"B05002_E009\", \"B05002_E010\", \"B05002_E011\", \"B05002_E012\", \"B05002_E013\", \"B05002_E014\", \"B05002_E015\", \"B05002_E016\", \"B05002_E017\", \"B05002_E018\", \"B05002_E019\", \"B05002_E020\", \"B05002_E021\", \"B05002_E022\", \"B05002_E023\", \"B05002_E024\", \"B05002_E025\", \"B05002_E026\", \"B05002_E027\", \"B05011_E001\", \"B05011_E002\", \"B05011_E003\", \"B05011_E004\", \"B05011_E005\", \"B05011_E006\", \"B05011_E007\", \"B05011_E008\", \"B05011_E009\", \"B05011_E010\", \"B05012_E001\", \"B05012_E002\", \"B05012_E003\", \"B06009_E001\", \"B06009_E002\", \"B06009_E003\", \"B06009_E004\", \"B06009_E005\", \"B06009_E006\", \"B06009_E007\", \"B06009_E008\", \"B06009_E009\", \"B06009_E010\", \"B06009_E011\", \"B06009_E012\", \"B06009_E013\", \"B06009_E014\", \"B06009_E015\", \"B06009_E016\", \"B06009_E017\", \"B06009_E018\", \"B06009_E019\", \"B06009_E020\", \"B06009_E021\", \"B06009_E022\", \"B06009_E023\", \"B06009_E024\", \"B06009_E025\", \"B06009_E026\", \"B06009_E027\", \"B06009_E028\", \"B06009_E029\", \"B06009_E030\", \"B06010_E001\", \"B06010_E002\", \"B06010_E003\", \"B06010_E004\", \"B06010_E005\", \"B06010_E006\", \"B06010_E007\", \"B06010_E008\", \"B06010_E009\", \"B06010_E010\", \"B06010_E011\", \"B06010_E012\", \"B06010_E013\", \"B06010_E014\", \"B06010_E015\", \"B06010_E016\", \"B06010_E017\", \"B06010_E018\", \"B06010_E019\", \"B06010_E020\", \"B06010_E021\", \"B06010_E022\", \"B06010_E023\", \"B06010_E024\", \"B06010_E025\", \"B06010_E026\", \"B06010_E027\", \"B06010_E028\", \"B06010_E029\", \"B06010_E030\", \"B06010_E031\", \"B06010_E032\", \"B06010_E033\", \"B06010_E034\", \"B06010_E035\", \"B06010_E036\", \"B06010_E037\", \"B06010_E038\", \"B06010_E039\", \"B06010_E040\", \"B06010_E041\", \"B06010_E042\", \"B06010_E043\", \"B06010_E044\", \"B06010_E045\", \"B06010_E046\", \"B06010_E047\", \"B06010_E048\", \"B06010_E049\", \"B06010_E050\", \"B06010_E051\", \"B06010_E052\", \"B06010_E053\", \"B06010_E054\", \"B06010_E055\", \"B06012_E001\", \"B06012_E002\", \"B06012_E003\", \"B06012_E004\", \"B06012_E005\", \"B06012_E006\", \"B06012_E007\", \"B06012_E008\", \"B06012_E009\", \"B06012_E010\", \"B06012_E011\", \"B06012_E012\", \"B06012_E013\", \"B06012_E014\", \"B06012_E015\", \"B06012_E016\", \"B06012_E017\", \"B06012_E018\", \"B06012_E019\", \"B06012_E020\", \"B08006_E001\", \"B08006_E002\", \"B08006_E003\", \"B08006_E004\", \"B08006_E005\", \"B08006_E006\", \"B08006_E007\", \"B08006_E008\", \"B08006_E009\", \"B08006_E010\", \"B08006_E011\", \"B08006_E012\", \"B08006_E013\", \"B08006_E014\", \"B08006_E015\", \"B08006_E016\", \"B08006_E017\", \"B08006_E018\", \"B08006_E019\", \"B08006_E020\", \"B08006_E021\", \"B08006_E022\", \"B08006_E023\", \"B08006_E024\", \"B08006_E025\", \"B08006_E026\", \"B08006_E027\", \"B08006_E028\", \"B08006_E029\", \"B08006_E030\", \"B08006_E031\", \"B08006_E032\", \"B08006_E033\", \"B08006_E034\", \"B08006_E035\", \"B08006_E036\", \"B08006_E037\", \"B08006_E038\", \"B08006_E039\", \"B08006_E040\", \"B08006_E041\", \"B08006_E042\", \"B08006_E043\", \"B08006_E044\", \"B08006_E045\", \"B08006_E046\", \"B08006_E047\", \"B08006_E048\", \"B08006_E049\", \"B08006_E050\", \"B08006_E051\", \"B08012_E001\", \"B08012_E002\", \"B08012_E003\", \"B08012_E004\", \"B08012_E005\", \"B08012_E006\", \"B08012_E007\", \"B08012_E008\", \"B08012_E009\", \"B08012_E010\", \"B08012_E011\", \"B08012_E012\", \"B08012_E013\", \"B08012_E014\", \"B08012_E015\", \"B08012_E016\", \"B08012_E017\", \"B08012_E018\", \"B08012_E019\", \"B08012_E020\", \"B08012_E021\", \"B08012_E022\", \"B08012_E023\", \"B08012_E024\", \"B08012_E025\", \"B08012_E026\", \"B08012_E027\", \"B08012_E028\", \"B08012_E029\", \"B08012_E030\", \"B08012_E031\", \"B08012_E032\", \"B08012_E033\", \"B08012_E034\", \"B08012_E035\", \"B08012_E036\", \"B08012_E037\", \"B08012_E038\", \"B08012_E039\", \"B08014_E001\", \"B08014_E002\", \"B08014_E003\", \"B08014_E004\", \"B08014_E005\", \"B08014_E006\", \"B08014_E007\", \"B08014_E008\", \"B08014_E009\", \"B08014_E010\", \"B08014_E011\", \"B08014_E012\", \"B08014_E013\", \"B08014_E014\", \"B08014_E015\", \"B08014_E016\", \"B08014_E017\", \"B08014_E018\", \"B08014_E019\", \"B08014_E020\", \"B08014_E021\", \"B08016_E001\", \"B08016_E002\", \"B08016_E003\", \"B08016_E004\", \"B08016_E005\", \"B08016_E006\", \"B08016_E007\", \"B08016_E008\", \"B08016_E009\", \"B08016_E010\", \"B08016_E011\", \"B08016_E012\", \"B08016_E013\", \"B08016_E014\", \"B08016_E015\", \"B08016_E016\", \"B08016_E017\", \"B08016_E018\", \"B08016_E019\", \"B08016_E020\", \"B08016_E021\", \"B08016_E022\", \"B08016_E023\", \"B08017_E001\", \"B08017_E002\", \"B08017_E003\", \"B08017_E004\", \"B08017_E005\", \"B08017_E006\", \"B08017_E007\", \"B08017_E008\", \"B08017_E009\", \"B08017_E010\", \"B08017_E011\", \"B08017_E012\", \"B08017_E013\", \"B08017_E014\", \"B08017_E015\", \"B08017_E016\", \"B08017_E017\", \"B08017_E018\", \"B08017_E019\", \"B08017_E020\", \"B08017_E021\", \"B08017_E022\", \"B08017_E023\", \"B08018_E001\", \"B08018_E002\", \"B08018_E003\", \"B08018_E004\", \"B08018_E005\", \"B08018_E006\", \"B08018_E007\", \"B08018_E008\", \"B08122_E001\", \"B08122_E002\", \"B08122_E003\", \"B08122_E004\", \"B08122_E005\", \"B08122_E006\", \"B08122_E007\", \"B08122_E008\", \"B08122_E009\", \"B08122_E010\", \"B08122_E011\", \"B08122_E012\", \"B08122_E013\", \"B08122_E014\", \"B08122_E015\", \"B08122_E016\", \"B08122_E017\", \"B08122_E018\", \"B08122_E019\", \"B08122_E020\", \"B08122_E021\", \"B08122_E022\", \"B08122_E023\", \"B08122_E024\", \"B08122_E025\", \"B08122_E026\", \"B08122_E027\", \"B08122_E028\", \"B08124_E001\", \"B08124_E002\", \"B08124_E003\", \"B08124_E004\", \"B08124_E005\", \"B08124_E006\", \"B08124_E007\", \"B08124_E008\", \"B08124_E009\", \"B08124_E010\", \"B08124_E011\", \"B08124_E012\", \"B08124_E013\", \"B08124_E014\", \"B08124_E015\", \"B08124_E016\", \"B08124_E017\", \"B08124_E018\", \"B08124_E019\", \"B08124_E020\", \"B08124_E021\", \"B08124_E022\", \"B08124_E023\", \"B08124_E024\", \"B08124_E025\", \"B08124_E026\", \"B08124_E027\", \"B08124_E028\", \"B08124_E029\", \"B08124_E030\", \"B08124_E031\", \"B08124_E032\", \"B08124_E033\", \"B08124_E034\", \"B08124_E035\", \"B08124_E036\", \"B08124_E037\", \"B08124_E038\", \"B08124_E039\", \"B08124_E040\", \"B08124_E041\", \"B08124_E042\", \"B08124_E043\", \"B08124_E044\", \"B08124_E045\", \"B08124_E046\", \"B08124_E047\", \"B08124_E048\", \"B08124_E049\", \"B08201_E001\", \"B08201_E002\", \"B08201_E003\", \"B08201_E004\", \"B08201_E005\", \"B08201_E006\", \"B08201_E007\", \"B08201_E008\", \"B08201_E009\", \"B08201_E010\", \"B08201_E011\", \"B08201_E012\", \"B08201_E013\", \"B08201_E014\", \"B08201_E015\", \"B08201_E016\", \"B08201_E017\", \"B08201_E018\", \"B08201_E019\", \"B08201_E020\", \"B08201_E021\", \"B08201_E022\", \"B08201_E023\", \"B08201_E024\", \"B08201_E025\", \"B08201_E026\", \"B08201_E027\", \"B08201_E028\", \"B08201_E029\", \"B08201_E030\", \"B08202_E001\", \"B08202_E002\", \"B08202_E003\", \"B08202_E004\", \"B08202_E005\", \"B08202_E006\", \"B08202_E007\", \"B08202_E008\", \"B08202_E009\", \"B08202_E010\", \"B08202_E011\", \"B08202_E012\", \"B08202_E013\", \"B08202_E014\", \"B08202_E015\", \"B08202_E016\", \"B08202_E017\", \"B08202_E018\", \"B08202_E019\", \"B08202_E020\", \"B08202_E021\", \"B08202_E022\", \"B08203_E001\", \"B08203_E002\", \"B08203_E003\", \"B08203_E004\", \"B08203_E005\", \"B08203_E006\", \"B08203_E007\", \"B08203_E008\", \"B08203_E009\", \"B08203_E010\", \"B08203_E011\", \"B08203_E012\", \"B08203_E013\", \"B08203_E014\", \"B08203_E015\", \"B08203_E016\", \"B08203_E017\", \"B08203_E018\", \"B08203_E019\", \"B08203_E020\", \"B08203_E021\", \"B08203_E022\", \"B08203_E023\", \"B08203_E024\", \"B08203_E025\", \"B08203_E026\", \"B08203_E027\", \"B08203_E028\", \"B08203_E029\", \"B08203_E030\", \"B08301_E001\", \"B08301_E002\", \"B08301_E003\", \"B08301_E004\", \"B08301_E005\", \"B08301_E006\", \"B08301_E007\", \"B08301_E008\", \"B08301_E009\", \"B08301_E010\", \"B08301_E011\", \"B08301_E012\", \"B08301_E013\", \"B08301_E014\", \"B08301_E015\", \"B08301_E016\", \"B08301_E017\", \"B08301_E018\", \"B08301_E019\", \"B08301_E020\", \"B08301_E021\", \"B08303_E001\", \"B08303_E002\", \"B08303_E003\", \"B08303_E004\", \"B08303_E005\", \"B08303_E006\", \"B08303_E007\", \"B08303_E008\", \"B08303_E009\", \"B08303_E010\", \"B08303_E011\", \"B08303_E012\", \"B08303_E013\", \"B15002_E001\", \"B15002_E002\", \"B15002_E003\", \"B15002_E004\", \"B15002_E005\", \"B15002_E006\", \"B15002_E007\", \"B15002_E008\", \"B15002_E009\", \"B15002_E010\", \"B15002_E011\", \"B15002_E012\", \"B15002_E013\", \"B15002_E014\", \"B15002_E015\", \"B15002_E016\", \"B15002_E017\", \"B15002_E018\", \"B15002_E019\", \"B15002_E020\", \"B15002_E021\", \"B15002_E022\", \"B15002_E023\", \"B15002_E024\", \"B15002_E025\", \"B15002_E026\", \"B15002_E027\", \"B15002_E028\", \"B15002_E029\", \"B15002_E030\", \"B15002_E031\", \"B15002_E032\", \"B15002_E033\", \"B15002_E034\", \"B15002_E035\", \"B15003_E001\", \"B15003_E002\", \"B15003_E003\", \"B15003_E004\", \"B15003_E005\", \"B15003_E006\", \"B15003_E007\", \"B15003_E008\", \"B15003_E009\", \"B15003_E010\", \"B15003_E011\", \"B15003_E012\", \"B15003_E013\", \"B15003_E014\", \"B15003_E015\", \"B15003_E016\", \"B15003_E017\", \"B15003_E018\", \"B15003_E019\", \"B15003_E020\", \"B15003_E021\", \"B15003_E022\", \"B15003_E023\", \"B15003_E024\", \"B15003_E025\", \"B15011_E001\", \"B15011_E002\", \"B15011_E003\", \"B15011_E004\", \"B15011_E005\", \"B15011_E006\", \"B15011_E007\", \"B15011_E008\", \"B15011_E009\", \"B15011_E010\", \"B15011_E011\", \"B15011_E012\", \"B15011_E013\", \"B15011_E014\", \"B15011_E015\", \"B15011_E016\", \"B15011_E017\", \"B15011_E018\", \"B15011_E019\", \"B15011_E020\", \"B15011_E021\", \"B15011_E022\", \"B15011_E023\", \"B15011_E024\", \"B15011_E025\", \"B15011_E026\", \"B15011_E027\", \"B15011_E028\", \"B15011_E029\", \"B15011_E030\", \"B15011_E031\", \"B15011_E032\", \"B15011_E033\", \"B15011_E034\", \"B15011_E035\", \"B15011_E036\", \"B15011_E037\", \"B15011_E038\", \"B15011_E039\", \"B17020_E001\", \"B17020_E002\", \"B17020_E003\", \"B17020_E004\", \"B17020_E005\", \"B17020_E006\", \"B17020_E007\", \"B17020_E008\", \"B17020_E009\", \"B17020_E010\", \"B17020_E011\", \"B17020_E012\", \"B17020_E013\", \"B17020_E014\", \"B17020_E015\", \"B17020_E016\", \"B17020_E017\", \"B19013_E001\", \"B19019_E001\", \"B19019_E002\", \"B19019_E003\", \"B19019_E004\", \"B19019_E005\", \"B19019_E006\", \"B19019_E007\", \"B19019_E008\", \"B19049_E001\", \"B19049_E002\", \"B19049_E003\", \"B19049_E004\", \"B19049_E005\", \"B19081_E001\", \"B19081_E002\", \"B19081_E003\", \"B19081_E004\", \"B19081_E005\", \"B19081_E006\", \"B19101_E001\", \"B19101_E002\", \"B19101_E003\", \"B19101_E004\", \"B19101_E005\", \"B19101_E006\", \"B19101_E007\", \"B19101_E008\", \"B19101_E009\", \"B19101_E010\", \"B19101_E011\", \"B19101_E012\", \"B19101_E013\", \"B19101_E014\", \"B19101_E015\", \"B19101_E016\", \"B19101_E017\", \"B19113_E001\", \"B19119_E001\", \"B19119_E002\", \"B19119_E003\", \"B19119_E004\", \"B19119_E005\", \"B19119_E006\", \"B19119_E007\", \"B19121_E001\", \"B19121_E002\", \"B19121_E003\", \"B19121_E004\", \"B19121_E005\", \"B19125_E001\", \"B19125_E002\", \"B19125_E003\", \"B19126_E001\", \"B19126_E002\", \"B19126_E003\", \"B19126_E004\", \"B19126_E005\", \"B19126_E006\", \"B19126_E007\", \"B19126_E008\", \"B19126_E009\", \"B19126_E010\", \"B19126_E011\", \"B19301_E001\", \"B23020_E001\", \"B23020_E002\", \"B23020_E003\", \"B23027_E001\", \"B23027_E002\", \"B23027_E003\", \"B23027_E004\", \"B23027_E005\", \"B23027_E006\", \"B23027_E007\", \"B23027_E008\", \"B23027_E009\", \"B23027_E010\", \"B23027_E011\", \"B23027_E012\", \"B23027_E013\", \"B23027_E014\", \"B23027_E015\", \"B23027_E016\", \"B23027_E017\", \"B23027_E018\", \"B23027_E019\", \"B23027_E020\", \"B23027_E021\", \"B23027_E022\", \"B23027_E023\", \"B23027_E024\", \"B23027_E025\", \"B23027_E026\", \"B23027_E027\", \"B23027_E028\", \"B23027_E029\", \"B23027_E030\", \"B23027_E031\", \"B23027_E032\", \"B23027_E033\", \"B23027_E034\", \"B23027_E035\", \"B23027_E036\", \"B25002_E001\", \"B25002_E002\", \"B25002_E003\", \"B25018_E001\", \"B25040_E001\", \"B25040_E002\", \"B25040_E003\", \"B25040_E004\", \"B25040_E005\", \"B25040_E006\", \"B25040_E007\", \"B25040_E008\", \"B25040_E009\", \"B25040_E010\", \"B25071_E001\", \"B25077_E001\", \"B25104_E001\", \"B25104_E002\", \"B25104_E003\", \"B25104_E004\", \"B25104_E005\", \"B25104_E006\", \"B25104_E007\", \"B25104_E008\", \"B25104_E009\", \"B25104_E010\", \"B25104_E011\", \"B25104_E012\", \"B25104_E013\", \"B25104_E014\", \"B25104_E015\", \"B25104_E016\", \"B25104_E017\", \"B25132_E001\", \"B25132_E002\", \"B25132_E003\", \"B25132_E004\", \"B25132_E005\", \"B25132_E006\", \"B25132_E007\", \"B25132_E008\", \"B25132_E009\", \"B25133_E001\", \"B25133_E002\", \"B25133_E003\", \"B25133_E004\", \"B25133_E005\", \"B25133_E006\", \"B25133_E007\", \"B25133_E008\", \"B25133_E009\", \"B28010_E001\", \"B28010_E002\", \"B28010_E003\", \"B28010_E004\", \"B28010_E005\", \"B28010_E006\", \"B28010_E007\", \"B28011_E001\", \"B28011_E002\", \"B28011_E003\", \"B28011_E004\", \"B28011_E005\", \"B28011_E006\", \"B28011_E007\", \"B28011_E008\", \"C15010_E001\", \"C15010_E002\", \"C15010_E003\", \"C15010_E004\", \"C15010_E005\", \"C15010_E006\", \"C17002_E001\", \"C17002_E002\", \"C17002_E003\", \"C17002_E004\", \"C17002_E005\", \"C17002_E006\", \"C17002_E007\", \"C17002_E008\", \"C24050_E001\", \"C24050_E002\", \"C24050_E003\", \"C24050_E004\", \"C24050_E005\", \"C24050_E006\", \"C24050_E007\", \"C24050_E008\", \"C24050_E009\", \"C24050_E010\", \"C24050_E011\", \"C24050_E012\", \"C24050_E013\", \"C24050_E014\", \"C24050_E015\", \"C24050_E016\", \"C24050_E017\", \"C24050_E018\", \"C24050_E019\", \"C24050_E020\", \"C24050_E021\", \"C24050_E022\", \"C24050_E023\", \"C24050_E024\", \"C24050_E025\", \"C24050_E026\", \"C24050_E027\", \"C24050_E028\", \"C24050_E029\", \"C24050_E030\", \"C24050_E031\", \"C24050_E032\", \"C24050_E033\", \"C24050_E034\", \"C24050_E035\", \"C24050_E036\", \"C24050_E037\", \"C24050_E038\", \"C24050_E039\", \"C24050_E040\", \"C24050_E041\", \"C24050_E042\", \"C24050_E043\", \"C24050_E044\", \"C24050_E045\", \"C24050_E046\", \"C24050_E047\", \"C24050_E048\", \"C24050_E049\", \"C24050_E050\", \"C24050_E051\", \"C24050_E052\", \"C24050_E053\", \"C24050_E054\", \"C24050_E055\", \"C24050_E056\", \"C24050_E057\", \"C24050_E058\", \"C24050_E059\", \"C24050_E060\", \"C24050_E061\", \"C24050_E062\", \"C24050_E063\", \"C24050_E064\", \"C24050_E065\", \"C24050_E066\", \"C24050_E067\", \"C24050_E068\", \"C24050_E069\", \"C24050_E070\", \"C24050_E071\", \"C24050_E072\", \"C24050_E073\", \"C24050_E074\", \"C24050_E075\", \"C24050_E076\", \"C24050_E077\", \"C24050_E078\", \"C24050_E079\", \"C24050_E080\", \"C24050_E081\", \"C24050_E082\", \"C24050_E083\", \"C24050_E084\", \"LandArea\", \"PopulationDensity\"]",
     "output_type": "error",
     "traceback": [
      "\u001b[31m---------------------------------------------------------------------------\u001b[39m",
      "\u001b[31mColumnNotFoundError\u001b[39m                       Traceback (most recent call last)",
      "\u001b[36mCell\u001b[39m\u001b[36m \u001b[39m\u001b[32mIn[16]\u001b[39m\u001b[32m, line 3\u001b[39m\n\u001b[32m      1\u001b[39m \u001b[38;5;66;03m# Filter to the three variables of interest\u001b[39;00m\n\u001b[32m      2\u001b[39m acs_vars = [\u001b[33m\"\u001b[39m\u001b[33mB01003_E001\u001b[39m\u001b[33m\"\u001b[39m, \u001b[33m\"\u001b[39m\u001b[33mLandArea\u001b[39m\u001b[33m\"\u001b[39m, \u001b[33m\"\u001b[39m\u001b[33mPopulationDensity\u001b[39m\u001b[33m\"\u001b[39m]\n\u001b[32m----> \u001b[39m\u001b[32m3\u001b[39m acs_filtered = \u001b[43macs_raw\u001b[49m\u001b[43m.\u001b[49m\u001b[43mfilter\u001b[49m\u001b[43m(\u001b[49m\u001b[43mpl\u001b[49m\u001b[43m.\u001b[49m\u001b[43mcol\u001b[49m\u001b[43m(\u001b[49m\u001b[33;43m\"\u001b[39;49m\u001b[33;43mVariable\u001b[39;49m\u001b[33;43m\"\u001b[39;49m\u001b[43m)\u001b[49m\u001b[43m.\u001b[49m\u001b[43mis_in\u001b[49m\u001b[43m(\u001b[49m\u001b[43macs_vars\u001b[49m\u001b[43m)\u001b[49m\u001b[43m)\u001b[49m\n\u001b[32m      4\u001b[39m \u001b[38;5;28mprint\u001b[39m(\u001b[33m\"\u001b[39m\u001b[33mFiltered to\u001b[39m\u001b[33m\"\u001b[39m, acs_filtered.height, \u001b[33m\"\u001b[39m\u001b[33mrows (variables)\u001b[39m\u001b[33m\"\u001b[39m)\n\u001b[32m      5\u001b[39m acs_filtered.select(\u001b[33m\"\u001b[39m\u001b[33mVariable\u001b[39m\u001b[33m\"\u001b[39m)\n",
      "\u001b[36mFile \u001b[39m\u001b[32m~\\AppData\\Local\\miniconda3\\envs\\py3123\\Lib\\site-packages\\polars\\dataframe\\frame.py:5321\u001b[39m, in \u001b[36mDataFrame.filter\u001b[39m\u001b[34m(self, *predicates, **constraints)\u001b[39m\n\u001b[32m   5161\u001b[39m \u001b[38;5;250m\u001b[39m\u001b[33;03m\"\"\"\u001b[39;00m\n\u001b[32m   5162\u001b[39m \u001b[33;03mFilter rows, retaining those that match the given predicate expression(s).\u001b[39;00m\n\u001b[32m   5163\u001b[39m \n\u001b[32m   (...)\u001b[39m\u001b[32m   5314\u001b[39m \u001b[33;03m└──────┴──────┴─────┘\u001b[39;00m\n\u001b[32m   5315\u001b[39m \u001b[33;03m\"\"\"\u001b[39;00m\n\u001b[32m   5316\u001b[39m \u001b[38;5;28;01mfrom\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[34;01mpolars\u001b[39;00m\u001b[34;01m.\u001b[39;00m\u001b[34;01mlazyframe\u001b[39;00m\u001b[34;01m.\u001b[39;00m\u001b[34;01mopt_flags\u001b[39;00m\u001b[38;5;250m \u001b[39m\u001b[38;5;28;01mimport\u001b[39;00m QueryOptFlags\n\u001b[32m   5318\u001b[39m \u001b[38;5;28;01mreturn\u001b[39;00m (\n\u001b[32m   5319\u001b[39m     \u001b[38;5;28;43mself\u001b[39;49m\u001b[43m.\u001b[49m\u001b[43mlazy\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\n\u001b[32m   5320\u001b[39m \u001b[43m    \u001b[49m\u001b[43m.\u001b[49m\u001b[43mfilter\u001b[49m\u001b[43m(\u001b[49m\u001b[43m*\u001b[49m\u001b[43mpredicates\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43m*\u001b[49m\u001b[43m*\u001b[49m\u001b[43mconstraints\u001b[49m\u001b[43m)\u001b[49m\n\u001b[32m-> \u001b[39m\u001b[32m5321\u001b[39m \u001b[43m    \u001b[49m\u001b[43m.\u001b[49m\u001b[43mcollect\u001b[49m\u001b[43m(\u001b[49m\u001b[43moptimizations\u001b[49m\u001b[43m=\u001b[49m\u001b[43mQueryOptFlags\u001b[49m\u001b[43m.\u001b[49m\u001b[43m_eager\u001b[49m\u001b[43m(\u001b[49m\u001b[43m)\u001b[49m\u001b[43m)\u001b[49m\n\u001b[32m   5322\u001b[39m )\n",
      "\u001b[36mFile \u001b[39m\u001b[32m~\\AppData\\Local\\miniconda3\\envs\\py3123\\Lib\\site-packages\\polars\\_utils\\deprecation.py:97\u001b[39m, in \u001b[36mdeprecate_streaming_parameter.<locals>.decorate.<locals>.wrapper\u001b[39m\u001b[34m(*args, **kwargs)\u001b[39m\n\u001b[32m     93\u001b[39m         kwargs[\u001b[33m\"\u001b[39m\u001b[33mengine\u001b[39m\u001b[33m\"\u001b[39m] = \u001b[33m\"\u001b[39m\u001b[33min-memory\u001b[39m\u001b[33m\"\u001b[39m\n\u001b[32m     95\u001b[39m     \u001b[38;5;28;01mdel\u001b[39;00m kwargs[\u001b[33m\"\u001b[39m\u001b[33mstreaming\u001b[39m\u001b[33m\"\u001b[39m]\n\u001b[32m---> \u001b[39m\u001b[32m97\u001b[39m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43mfunction\u001b[49m\u001b[43m(\u001b[49m\u001b[43m*\u001b[49m\u001b[43margs\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43m*\u001b[49m\u001b[43m*\u001b[49m\u001b[43mkwargs\u001b[49m\u001b[43m)\u001b[49m\n",
      "\u001b[36mFile \u001b[39m\u001b[32m~\\AppData\\Local\\miniconda3\\envs\\py3123\\Lib\\site-packages\\polars\\lazyframe\\opt_flags.py:328\u001b[39m, in \u001b[36mforward_old_opt_flags.<locals>.decorate.<locals>.wrapper\u001b[39m\u001b[34m(*args, **kwargs)\u001b[39m\n\u001b[32m    325\u001b[39m         optflags = cb(optflags, kwargs.pop(key))  \u001b[38;5;66;03m# type: ignore[no-untyped-call,unused-ignore]\u001b[39;00m\n\u001b[32m    327\u001b[39m kwargs[\u001b[33m\"\u001b[39m\u001b[33moptimizations\u001b[39m\u001b[33m\"\u001b[39m] = optflags\n\u001b[32m--> \u001b[39m\u001b[32m328\u001b[39m \u001b[38;5;28;01mreturn\u001b[39;00m \u001b[43mfunction\u001b[49m\u001b[43m(\u001b[49m\u001b[43m*\u001b[49m\u001b[43margs\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43m*\u001b[49m\u001b[43m*\u001b[49m\u001b[43mkwargs\u001b[49m\u001b[43m)\u001b[49m\n",
      "\u001b[36mFile \u001b[39m\u001b[32m~\\AppData\\Local\\miniconda3\\envs\\py3123\\Lib\\site-packages\\polars\\lazyframe\\frame.py:2422\u001b[39m, in \u001b[36mLazyFrame.collect\u001b[39m\u001b[34m(self, type_coercion, predicate_pushdown, projection_pushdown, simplify_expression, slice_pushdown, comm_subplan_elim, comm_subexpr_elim, cluster_with_columns, collapse_joins, no_optimization, engine, background, optimizations, **_kwargs)\u001b[39m\n\u001b[32m   2420\u001b[39m \u001b[38;5;66;03m# Only for testing purposes\u001b[39;00m\n\u001b[32m   2421\u001b[39m callback = _kwargs.get(\u001b[33m\"\u001b[39m\u001b[33mpost_opt_callback\u001b[39m\u001b[33m\"\u001b[39m, callback)\n\u001b[32m-> \u001b[39m\u001b[32m2422\u001b[39m \u001b[38;5;28;01mreturn\u001b[39;00m wrap_df(\u001b[43mldf\u001b[49m\u001b[43m.\u001b[49m\u001b[43mcollect\u001b[49m\u001b[43m(\u001b[49m\u001b[43mengine\u001b[49m\u001b[43m,\u001b[49m\u001b[43m \u001b[49m\u001b[43mcallback\u001b[49m\u001b[43m)\u001b[49m)\n",
      "\u001b[31mColumnNotFoundError\u001b[39m: unable to find column \"Variable\"; valid columns: [\"Location\", \"B01002_E001\", \"B01002_E002\", \"B01002_E003\", \"B01003_E001\", \"B02001_E001\", \"B02001_E002\", \"B02001_E003\", \"B02001_E004\", \"B02001_E005\", \"B02001_E006\", \"B02001_E007\", \"B02001_E008\", \"B02001_E009\", \"B02001_E010\", \"B05001_E001\", \"B05001_E002\", \"B05001_E003\", \"B05001_E004\", \"B05001_E005\", \"B05001_E006\", \"B05002_E001\", \"B05002_E002\", \"B05002_E003\", \"B05002_E004\", \"B05002_E005\", \"B05002_E006\", \"B05002_E007\", \"B05002_E008\", \"B05002_E009\", \"B05002_E010\", \"B05002_E011\", \"B05002_E012\", \"B05002_E013\", \"B05002_E014\", \"B05002_E015\", \"B05002_E016\", \"B05002_E017\", \"B05002_E018\", \"B05002_E019\", \"B05002_E020\", \"B05002_E021\", \"B05002_E022\", \"B05002_E023\", \"B05002_E024\", \"B05002_E025\", \"B05002_E026\", \"B05002_E027\", \"B05011_E001\", \"B05011_E002\", \"B05011_E003\", \"B05011_E004\", \"B05011_E005\", \"B05011_E006\", \"B05011_E007\", \"B05011_E008\", \"B05011_E009\", \"B05011_E010\", \"B05012_E001\", \"B05012_E002\", \"B05012_E003\", \"B06009_E001\", \"B06009_E002\", \"B06009_E003\", \"B06009_E004\", \"B06009_E005\", \"B06009_E006\", \"B06009_E007\", \"B06009_E008\", \"B06009_E009\", \"B06009_E010\", \"B06009_E011\", \"B06009_E012\", \"B06009_E013\", \"B06009_E014\", \"B06009_E015\", \"B06009_E016\", \"B06009_E017\", \"B06009_E018\", \"B06009_E019\", \"B06009_E020\", \"B06009_E021\", \"B06009_E022\", \"B06009_E023\", \"B06009_E024\", \"B06009_E025\", \"B06009_E026\", \"B06009_E027\", \"B06009_E028\", \"B06009_E029\", \"B06009_E030\", \"B06010_E001\", \"B06010_E002\", \"B06010_E003\", \"B06010_E004\", \"B06010_E005\", \"B06010_E006\", \"B06010_E007\", \"B06010_E008\", \"B06010_E009\", \"B06010_E010\", \"B06010_E011\", \"B06010_E012\", \"B06010_E013\", \"B06010_E014\", \"B06010_E015\", \"B06010_E016\", \"B06010_E017\", \"B06010_E018\", \"B06010_E019\", \"B06010_E020\", \"B06010_E021\", \"B06010_E022\", \"B06010_E023\", \"B06010_E024\", \"B06010_E025\", \"B06010_E026\", \"B06010_E027\", \"B06010_E028\", \"B06010_E029\", \"B06010_E030\", \"B06010_E031\", \"B06010_E032\", \"B06010_E033\", \"B06010_E034\", \"B06010_E035\", \"B06010_E036\", \"B06010_E037\", \"B06010_E038\", \"B06010_E039\", \"B06010_E040\", \"B06010_E041\", \"B06010_E042\", \"B06010_E043\", \"B06010_E044\", \"B06010_E045\", \"B06010_E046\", \"B06010_E047\", \"B06010_E048\", \"B06010_E049\", \"B06010_E050\", \"B06010_E051\", \"B06010_E052\", \"B06010_E053\", \"B06010_E054\", \"B06010_E055\", \"B06012_E001\", \"B06012_E002\", \"B06012_E003\", \"B06012_E004\", \"B06012_E005\", \"B06012_E006\", \"B06012_E007\", \"B06012_E008\", \"B06012_E009\", \"B06012_E010\", \"B06012_E011\", \"B06012_E012\", \"B06012_E013\", \"B06012_E014\", \"B06012_E015\", \"B06012_E016\", \"B06012_E017\", \"B06012_E018\", \"B06012_E019\", \"B06012_E020\", \"B08006_E001\", \"B08006_E002\", \"B08006_E003\", \"B08006_E004\", \"B08006_E005\", \"B08006_E006\", \"B08006_E007\", \"B08006_E008\", \"B08006_E009\", \"B08006_E010\", \"B08006_E011\", \"B08006_E012\", \"B08006_E013\", \"B08006_E014\", \"B08006_E015\", \"B08006_E016\", \"B08006_E017\", \"B08006_E018\", \"B08006_E019\", \"B08006_E020\", \"B08006_E021\", \"B08006_E022\", \"B08006_E023\", \"B08006_E024\", \"B08006_E025\", \"B08006_E026\", \"B08006_E027\", \"B08006_E028\", \"B08006_E029\", \"B08006_E030\", \"B08006_E031\", \"B08006_E032\", \"B08006_E033\", \"B08006_E034\", \"B08006_E035\", \"B08006_E036\", \"B08006_E037\", \"B08006_E038\", \"B08006_E039\", \"B08006_E040\", \"B08006_E041\", \"B08006_E042\", \"B08006_E043\", \"B08006_E044\", \"B08006_E045\", \"B08006_E046\", \"B08006_E047\", \"B08006_E048\", \"B08006_E049\", \"B08006_E050\", \"B08006_E051\", \"B08012_E001\", \"B08012_E002\", \"B08012_E003\", \"B08012_E004\", \"B08012_E005\", \"B08012_E006\", \"B08012_E007\", \"B08012_E008\", \"B08012_E009\", \"B08012_E010\", \"B08012_E011\", \"B08012_E012\", \"B08012_E013\", \"B08012_E014\", \"B08012_E015\", \"B08012_E016\", \"B08012_E017\", \"B08012_E018\", \"B08012_E019\", \"B08012_E020\", \"B08012_E021\", \"B08012_E022\", \"B08012_E023\", \"B08012_E024\", \"B08012_E025\", \"B08012_E026\", \"B08012_E027\", \"B08012_E028\", \"B08012_E029\", \"B08012_E030\", \"B08012_E031\", \"B08012_E032\", \"B08012_E033\", \"B08012_E034\", \"B08012_E035\", \"B08012_E036\", \"B08012_E037\", \"B08012_E038\", \"B08012_E039\", \"B08014_E001\", \"B08014_E002\", \"B08014_E003\", \"B08014_E004\", \"B08014_E005\", \"B08014_E006\", \"B08014_E007\", \"B08014_E008\", \"B08014_E009\", \"B08014_E010\", \"B08014_E011\", \"B08014_E012\", \"B08014_E013\", \"B08014_E014\", \"B08014_E015\", \"B08014_E016\", \"B08014_E017\", \"B08014_E018\", \"B08014_E019\", \"B08014_E020\", \"B08014_E021\", \"B08016_E001\", \"B08016_E002\", \"B08016_E003\", \"B08016_E004\", \"B08016_E005\", \"B08016_E006\", \"B08016_E007\", \"B08016_E008\", \"B08016_E009\", \"B08016_E010\", \"B08016_E011\", \"B08016_E012\", \"B08016_E013\", \"B08016_E014\", \"B08016_E015\", \"B08016_E016\", \"B08016_E017\", \"B08016_E018\", \"B08016_E019\", \"B08016_E020\", \"B08016_E021\", \"B08016_E022\", \"B08016_E023\", \"B08017_E001\", \"B08017_E002\", \"B08017_E003\", \"B08017_E004\", \"B08017_E005\", \"B08017_E006\", \"B08017_E007\", \"B08017_E008\", \"B08017_E009\", \"B08017_E010\", \"B08017_E011\", \"B08017_E012\", \"B08017_E013\", \"B08017_E014\", \"B08017_E015\", \"B08017_E016\", \"B08017_E017\", \"B08017_E018\", \"B08017_E019\", \"B08017_E020\", \"B08017_E021\", \"B08017_E022\", \"B08017_E023\", \"B08018_E001\", \"B08018_E002\", \"B08018_E003\", \"B08018_E004\", \"B08018_E005\", \"B08018_E006\", \"B08018_E007\", \"B08018_E008\", \"B08122_E001\", \"B08122_E002\", \"B08122_E003\", \"B08122_E004\", \"B08122_E005\", \"B08122_E006\", \"B08122_E007\", \"B08122_E008\", \"B08122_E009\", \"B08122_E010\", \"B08122_E011\", \"B08122_E012\", \"B08122_E013\", \"B08122_E014\", \"B08122_E015\", \"B08122_E016\", \"B08122_E017\", \"B08122_E018\", \"B08122_E019\", \"B08122_E020\", \"B08122_E021\", \"B08122_E022\", \"B08122_E023\", \"B08122_E024\", \"B08122_E025\", \"B08122_E026\", \"B08122_E027\", \"B08122_E028\", \"B08124_E001\", \"B08124_E002\", \"B08124_E003\", \"B08124_E004\", \"B08124_E005\", \"B08124_E006\", \"B08124_E007\", \"B08124_E008\", \"B08124_E009\", \"B08124_E010\", \"B08124_E011\", \"B08124_E012\", \"B08124_E013\", \"B08124_E014\", \"B08124_E015\", \"B08124_E016\", \"B08124_E017\", \"B08124_E018\", \"B08124_E019\", \"B08124_E020\", \"B08124_E021\", \"B08124_E022\", \"B08124_E023\", \"B08124_E024\", \"B08124_E025\", \"B08124_E026\", \"B08124_E027\", \"B08124_E028\", \"B08124_E029\", \"B08124_E030\", \"B08124_E031\", \"B08124_E032\", \"B08124_E033\", \"B08124_E034\", \"B08124_E035\", \"B08124_E036\", \"B08124_E037\", \"B08124_E038\", \"B08124_E039\", \"B08124_E040\", \"B08124_E041\", \"B08124_E042\", \"B08124_E043\", \"B08124_E044\", \"B08124_E045\", \"B08124_E046\", \"B08124_E047\", \"B08124_E048\", \"B08124_E049\", \"B08201_E001\", \"B08201_E002\", \"B08201_E003\", \"B08201_E004\", \"B08201_E005\", \"B08201_E006\", \"B08201_E007\", \"B08201_E008\", \"B08201_E009\", \"B08201_E010\", \"B08201_E011\", \"B08201_E012\", \"B08201_E013\", \"B08201_E014\", \"B08201_E015\", \"B08201_E016\", \"B08201_E017\", \"B08201_E018\", \"B08201_E019\", \"B08201_E020\", \"B08201_E021\", \"B08201_E022\", \"B08201_E023\", \"B08201_E024\", \"B08201_E025\", \"B08201_E026\", \"B08201_E027\", \"B08201_E028\", \"B08201_E029\", \"B08201_E030\", \"B08202_E001\", \"B08202_E002\", \"B08202_E003\", \"B08202_E004\", \"B08202_E005\", \"B08202_E006\", \"B08202_E007\", \"B08202_E008\", \"B08202_E009\", \"B08202_E010\", \"B08202_E011\", \"B08202_E012\", \"B08202_E013\", \"B08202_E014\", \"B08202_E015\", \"B08202_E016\", \"B08202_E017\", \"B08202_E018\", \"B08202_E019\", \"B08202_E020\", \"B08202_E021\", \"B08202_E022\", \"B08203_E001\", \"B08203_E002\", \"B08203_E003\", \"B08203_E004\", \"B08203_E005\", \"B08203_E006\", \"B08203_E007\", \"B08203_E008\", \"B08203_E009\", \"B08203_E010\", \"B08203_E011\", \"B08203_E012\", \"B08203_E013\", \"B08203_E014\", \"B08203_E015\", \"B08203_E016\", \"B08203_E017\", \"B08203_E018\", \"B08203_E019\", \"B08203_E020\", \"B08203_E021\", \"B08203_E022\", \"B08203_E023\", \"B08203_E024\", \"B08203_E025\", \"B08203_E026\", \"B08203_E027\", \"B08203_E028\", \"B08203_E029\", \"B08203_E030\", \"B08301_E001\", \"B08301_E002\", \"B08301_E003\", \"B08301_E004\", \"B08301_E005\", \"B08301_E006\", \"B08301_E007\", \"B08301_E008\", \"B08301_E009\", \"B08301_E010\", \"B08301_E011\", \"B08301_E012\", \"B08301_E013\", \"B08301_E014\", \"B08301_E015\", \"B08301_E016\", \"B08301_E017\", \"B08301_E018\", \"B08301_E019\", \"B08301_E020\", \"B08301_E021\", \"B08303_E001\", \"B08303_E002\", \"B08303_E003\", \"B08303_E004\", \"B08303_E005\", \"B08303_E006\", \"B08303_E007\", \"B08303_E008\", \"B08303_E009\", \"B08303_E010\", \"B08303_E011\", \"B08303_E012\", \"B08303_E013\", \"B15002_E001\", \"B15002_E002\", \"B15002_E003\", \"B15002_E004\", \"B15002_E005\", \"B15002_E006\", \"B15002_E007\", \"B15002_E008\", \"B15002_E009\", \"B15002_E010\", \"B15002_E011\", \"B15002_E012\", \"B15002_E013\", \"B15002_E014\", \"B15002_E015\", \"B15002_E016\", \"B15002_E017\", \"B15002_E018\", \"B15002_E019\", \"B15002_E020\", \"B15002_E021\", \"B15002_E022\", \"B15002_E023\", \"B15002_E024\", \"B15002_E025\", \"B15002_E026\", \"B15002_E027\", \"B15002_E028\", \"B15002_E029\", \"B15002_E030\", \"B15002_E031\", \"B15002_E032\", \"B15002_E033\", \"B15002_E034\", \"B15002_E035\", \"B15003_E001\", \"B15003_E002\", \"B15003_E003\", \"B15003_E004\", \"B15003_E005\", \"B15003_E006\", \"B15003_E007\", \"B15003_E008\", \"B15003_E009\", \"B15003_E010\", \"B15003_E011\", \"B15003_E012\", \"B15003_E013\", \"B15003_E014\", \"B15003_E015\", \"B15003_E016\", \"B15003_E017\", \"B15003_E018\", \"B15003_E019\", \"B15003_E020\", \"B15003_E021\", \"B15003_E022\", \"B15003_E023\", \"B15003_E024\", \"B15003_E025\", \"B15011_E001\", \"B15011_E002\", \"B15011_E003\", \"B15011_E004\", \"B15011_E005\", \"B15011_E006\", \"B15011_E007\", \"B15011_E008\", \"B15011_E009\", \"B15011_E010\", \"B15011_E011\", \"B15011_E012\", \"B15011_E013\", \"B15011_E014\", \"B15011_E015\", \"B15011_E016\", \"B15011_E017\", \"B15011_E018\", \"B15011_E019\", \"B15011_E020\", \"B15011_E021\", \"B15011_E022\", \"B15011_E023\", \"B15011_E024\", \"B15011_E025\", \"B15011_E026\", \"B15011_E027\", \"B15011_E028\", \"B15011_E029\", \"B15011_E030\", \"B15011_E031\", \"B15011_E032\", \"B15011_E033\", \"B15011_E034\", \"B15011_E035\", \"B15011_E036\", \"B15011_E037\", \"B15011_E038\", \"B15011_E039\", \"B17020_E001\", \"B17020_E002\", \"B17020_E003\", \"B17020_E004\", \"B17020_E005\", \"B17020_E006\", \"B17020_E007\", \"B17020_E008\", \"B17020_E009\", \"B17020_E010\", \"B17020_E011\", \"B17020_E012\", \"B17020_E013\", \"B17020_E014\", \"B17020_E015\", \"B17020_E016\", \"B17020_E017\", \"B19013_E001\", \"B19019_E001\", \"B19019_E002\", \"B19019_E003\", \"B19019_E004\", \"B19019_E005\", \"B19019_E006\", \"B19019_E007\", \"B19019_E008\", \"B19049_E001\", \"B19049_E002\", \"B19049_E003\", \"B19049_E004\", \"B19049_E005\", \"B19081_E001\", \"B19081_E002\", \"B19081_E003\", \"B19081_E004\", \"B19081_E005\", \"B19081_E006\", \"B19101_E001\", \"B19101_E002\", \"B19101_E003\", \"B19101_E004\", \"B19101_E005\", \"B19101_E006\", \"B19101_E007\", \"B19101_E008\", \"B19101_E009\", \"B19101_E010\", \"B19101_E011\", \"B19101_E012\", \"B19101_E013\", \"B19101_E014\", \"B19101_E015\", \"B19101_E016\", \"B19101_E017\", \"B19113_E001\", \"B19119_E001\", \"B19119_E002\", \"B19119_E003\", \"B19119_E004\", \"B19119_E005\", \"B19119_E006\", \"B19119_E007\", \"B19121_E001\", \"B19121_E002\", \"B19121_E003\", \"B19121_E004\", \"B19121_E005\", \"B19125_E001\", \"B19125_E002\", \"B19125_E003\", \"B19126_E001\", \"B19126_E002\", \"B19126_E003\", \"B19126_E004\", \"B19126_E005\", \"B19126_E006\", \"B19126_E007\", \"B19126_E008\", \"B19126_E009\", \"B19126_E010\", \"B19126_E011\", \"B19301_E001\", \"B23020_E001\", \"B23020_E002\", \"B23020_E003\", \"B23027_E001\", \"B23027_E002\", \"B23027_E003\", \"B23027_E004\", \"B23027_E005\", \"B23027_E006\", \"B23027_E007\", \"B23027_E008\", \"B23027_E009\", \"B23027_E010\", \"B23027_E011\", \"B23027_E012\", \"B23027_E013\", \"B23027_E014\", \"B23027_E015\", \"B23027_E016\", \"B23027_E017\", \"B23027_E018\", \"B23027_E019\", \"B23027_E020\", \"B23027_E021\", \"B23027_E022\", \"B23027_E023\", \"B23027_E024\", \"B23027_E025\", \"B23027_E026\", \"B23027_E027\", \"B23027_E028\", \"B23027_E029\", \"B23027_E030\", \"B23027_E031\", \"B23027_E032\", \"B23027_E033\", \"B23027_E034\", \"B23027_E035\", \"B23027_E036\", \"B25002_E001\", \"B25002_E002\", \"B25002_E003\", \"B25018_E001\", \"B25040_E001\", \"B25040_E002\", \"B25040_E003\", \"B25040_E004\", \"B25040_E005\", \"B25040_E006\", \"B25040_E007\", \"B25040_E008\", \"B25040_E009\", \"B25040_E010\", \"B25071_E001\", \"B25077_E001\", \"B25104_E001\", \"B25104_E002\", \"B25104_E003\", \"B25104_E004\", \"B25104_E005\", \"B25104_E006\", \"B25104_E007\", \"B25104_E008\", \"B25104_E009\", \"B25104_E010\", \"B25104_E011\", \"B25104_E012\", \"B25104_E013\", \"B25104_E014\", \"B25104_E015\", \"B25104_E016\", \"B25104_E017\", \"B25132_E001\", \"B25132_E002\", \"B25132_E003\", \"B25132_E004\", \"B25132_E005\", \"B25132_E006\", \"B25132_E007\", \"B25132_E008\", \"B25132_E009\", \"B25133_E001\", \"B25133_E002\", \"B25133_E003\", \"B25133_E004\", \"B25133_E005\", \"B25133_E006\", \"B25133_E007\", \"B25133_E008\", \"B25133_E009\", \"B28010_E001\", \"B28010_E002\", \"B28010_E003\", \"B28010_E004\", \"B28010_E005\", \"B28010_E006\", \"B28010_E007\", \"B28011_E001\", \"B28011_E002\", \"B28011_E003\", \"B28011_E004\", \"B28011_E005\", \"B28011_E006\", \"B28011_E007\", \"B28011_E008\", \"C15010_E001\", \"C15010_E002\", \"C15010_E003\", \"C15010_E004\", \"C15010_E005\", \"C15010_E006\", \"C17002_E001\", \"C17002_E002\", \"C17002_E003\", \"C17002_E004\", \"C17002_E005\", \"C17002_E006\", \"C17002_E007\", \"C17002_E008\", \"C24050_E001\", \"C24050_E002\", \"C24050_E003\", \"C24050_E004\", \"C24050_E005\", \"C24050_E006\", \"C24050_E007\", \"C24050_E008\", \"C24050_E009\", \"C24050_E010\", \"C24050_E011\", \"C24050_E012\", \"C24050_E013\", \"C24050_E014\", \"C24050_E015\", \"C24050_E016\", \"C24050_E017\", \"C24050_E018\", \"C24050_E019\", \"C24050_E020\", \"C24050_E021\", \"C24050_E022\", \"C24050_E023\", \"C24050_E024\", \"C24050_E025\", \"C24050_E026\", \"C24050_E027\", \"C24050_E028\", \"C24050_E029\", \"C24050_E030\", \"C24050_E031\", \"C24050_E032\", \"C24050_E033\", \"C24050_E034\", \"C24050_E035\", \"C24050_E036\", \"C24050_E037\", \"C24050_E038\", \"C24050_E039\", \"C24050_E040\", \"C24050_E041\", \"C24050_E042\", \"C24050_E043\", \"C24050_E044\", \"C24050_E045\", \"C24050_E046\", \"C24050_E047\", \"C24050_E048\", \"C24050_E049\", \"C24050_E050\", \"C24050_E051\", \"C24050_E052\", \"C24050_E053\", \"C24050_E054\", \"C24050_E055\", \"C24050_E056\", \"C24050_E057\", \"C24050_E058\", \"C24050_E059\", \"C24050_E060\", \"C24050_E061\", \"C24050_E062\", \"C24050_E063\", \"C24050_E064\", \"C24050_E065\", \"C24050_E066\", \"C24050_E067\", \"C24050_E068\", \"C24050_E069\", \"C24050_E070\", \"C24050_E071\", \"C24050_E072\", \"C24050_E073\", \"C24050_E074\", \"C24050_E075\", \"C24050_E076\", \"C24050_E077\", \"C24050_E078\", \"C24050_E079\", \"C24050_E080\", \"C24050_E081\", \"C24050_E082\", \"C24050_E083\", \"C24050_E084\", \"LandArea\", \"PopulationDensity\"]"
     ]
    }
   ],
   "source": [
    "# Filter to the three variables of interest\n",
    "acs_vars = [\"B01003_E001\", \"LandArea\", \"PopulationDensity\"]\n",
    "acs_filtered = acs_raw.filter(pl.col(\"Variable\").is_in(acs_vars))\n",
    "print(\"Filtered to\", acs_filtered.height, \"rows (variables)\")\n",
    "acs_filtered.select(\"Variable\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ca611b31",
   "metadata": {},
   "source": [
    "### Transpose to one row per ZIP code\n",
    "\n",
    "After transposing, each former column header (a ZIP code string) becomes a row,\n",
    "and each former row (a variable name) becomes a column.  We cast the ZIP code\n",
    "column to `Int64` so it matches the type in the crosstabulation data, and cast\n",
    "the numeric columns to `Float64`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9565e55b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Transpose: columns become rows, Variable values become column headers\n",
    "acs_transposed = (\n",
    "    acs_filtered\n",
    "    .transpose(\n",
    "        include_header=True,\n",
    "        header_name=\"ZipCode\",\n",
    "        column_names=\"Variable\",\n",
    "    )\n",
    "    .with_columns(\n",
    "        pl.col(\"ZipCode\").cast(pl.Int64),\n",
    "        pl.col(\"B01003_E001\").cast(pl.Float64),\n",
    "        pl.col(\"LandArea\").cast(pl.Float64),\n",
    "        pl.col(\"PopulationDensity\").cast(pl.Float64),\n",
    "    )\n",
    "    .rename({\"B01003_E001\": \"Population\"})\n",
    "    .sort(\"ZipCode\")\n",
    ")\n",
    "\n",
    "print(\"ACS transposed shape:\", acs_transposed.shape)\n",
    "acs_transposed.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f525247c",
   "metadata": {},
   "source": [
    "## 2. Load the crosstabulation extract\n",
    "\n",
    "From `Crosstab_ALZipCodes.csv` (produced by `PivotEVAFuelType.ipynb`) we keep\n",
    "only the four columns needed for the comparison: `ZipCode`, `BEV`,\n",
    "`TotalVehicleCount`, and `BEVProportion`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7b3e54ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "crosstab = pl.read_csv(\"Crosstab_ALZipCodes.csv\")\n",
    "print(\"Crosstab raw shape:\", crosstab.shape)\n",
    "crosstab.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "756b4b0e",
   "metadata": {},
   "outputs": [],
   "source": [
    "crosstab_extract = crosstab.select(\"ZipCode\", \"BEV\", \"TotalVehicleCount\", \"BEVProportion\")\n",
    "print(\"Crosstab extract shape:\", crosstab_extract.shape)\n",
    "crosstab_extract.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "07defbbb",
   "metadata": {},
   "source": [
    "## 3. Outer join on ZipCode\n",
    "\n",
    "An outer join preserves every ZIP code from **both** sources:\n",
    "\n",
    "- ZIP codes in ACS but not in vehicle registrations will have `null` for BEV,\n",
    "  TotalVehicleCount, and BEVProportion.\n",
    "- ZIP codes in vehicle registrations but not in ACS will have `null` for\n",
    "  Population, LandArea, and PopulationDensity.\n",
    "\n",
    "The `coalesce=True` parameter merges the two `ZipCode` key columns into one."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3e05a198",
   "metadata": {},
   "outputs": [],
   "source": [
    "joined = (\n",
    "    acs_transposed\n",
    "    .join(crosstab_extract, on=\"ZipCode\", how=\"full\", coalesce=True)\n",
    "    .sort(\"ZipCode\")\n",
    ")\n",
    "\n",
    "print(\"Joined shape:\", joined.shape)\n",
    "joined.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b99d8b9d",
   "metadata": {},
   "source": [
    "### Check for mismatches\n",
    "\n",
    "Inspect rows where one side of the join has `null` values, to understand which\n",
    "ZIP codes exist in only one of the two sources."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f7f8c7c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "acs_only = joined.filter(pl.col(\"TotalVehicleCount\").is_null())\n",
    "ev_only = joined.filter(pl.col(\"Population\").is_null())\n",
    "both = joined.filter(pl.col(\"Population\").is_not_null() & pl.col(\"TotalVehicleCount\").is_not_null())\n",
    "\n",
    "print(f\"ZIP codes in both sources:    {both.height}\")\n",
    "print(f\"ZIP codes in ACS only:        {acs_only.height}\")\n",
    "print(f\"ZIP codes in EV data only:    {ev_only.height}\")\n",
    "print(f\"Total rows:                   {joined.height}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "sd5etr3z3ll",
   "metadata": {},
   "source": [
    "### Coverage loss by minimum-size threshold\n",
    "\n",
    "How much of each measure do we lose if we exclude ZIP codes that are too small\n",
    "or missing from one source?  A ZIP code is **excluded** when either its\n",
    "`Population` or `TotalVehicleCount` is `null` *or* falls below a given threshold.\n",
    "\n",
    "Each row in the table below represents a threshold value.  The columns show the\n",
    "**proportion lost** (0 = nothing lost, 1 = everything lost) for each measure.\n",
    "Threshold 0 corresponds to dropping only the truly missing ZIP codes (nulls);\n",
    "higher thresholds also drop small ZIP codes from both sides."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "k3h5ijh29bc",
   "metadata": {},
   "outputs": [],
   "source": [
    "thresholds = [0, 100, 200, 300, 400, 500, 600, 650, 675, 680, 690, 700]\n",
    "measures = [\"Population\", \"LandArea\", \"BEV\", \"TotalVehicleCount\"]\n",
    "\n",
    "# Universe totals: sum of each measure across all non-null ZIP codes\n",
    "totals = {m: joined.select(pl.col(m).sum()).item() for m in measures}\n",
    "\n",
    "rows = []\n",
    "for t in thresholds:\n",
    "    # Keep ZIP codes where both Population and TotalVehicleCount are non-null\n",
    "    # and at least as large as the threshold\n",
    "    kept = joined.filter(\n",
    "        (pl.col(\"Population\").is_not_null() & (pl.col(\"Population\") >= t))\n",
    "        & (pl.col(\"TotalVehicleCount\").is_not_null() & (pl.col(\"TotalVehicleCount\") >= t))\n",
    "    )\n",
    "    row = {\"Threshold\": t, \"ZipCodes Kept\": kept.height}\n",
    "    for m in measures:\n",
    "        retained = kept.select(pl.col(m).sum()).item()\n",
    "        row[m] = round((totals[m] - retained) / totals[m], 6) if totals[m] else 0.0\n",
    "    rows.append(row)\n",
    "\n",
    "loss_table = pl.DataFrame(rows)\n",
    "loss_table"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1acd599b",
   "metadata": {},
   "source": [
    "## 4. Save the result"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "18f4e7ce",
   "metadata": {},
   "outputs": [],
   "source": [
    "joined.write_csv(\"ZipCodeComparisons.csv\")\n",
    "print(\"Saved ZipCodeComparisons.csv\")\n",
    "print(\"Final columns:\", joined.columns)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1380488c",
   "metadata": {},
   "source": [
    "## Summary\n",
    "\n",
    "The output file `Crosstabulation/ZipCodeComparisons.csv` contains one row per\n",
    "Alabama ZIP code with the following columns:\n",
    "\n",
    "| Column | Source | Description |\n",
    "|---|---|---|\n",
    "| `ZipCode` | Both | ZIP code identifier |\n",
    "| `Population` | ACS (`B01003_E001`) | Total population |\n",
    "| `LandArea` | ACS | Land area in square miles |\n",
    "| `PopulationDensity` | ACS | Land area per capita (sq mi / person) |\n",
    "| `BEV` | Crosstab | Battery Electric Vehicle count |\n",
    "| `TotalVehicleCount` | Crosstab | Total registered vehicles |\n",
    "| `BEVProportion` | Crosstab | BEV / TotalVehicleCount |"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
